<?php
  require_once("inc/connect.php") ;

  $mysqli = SQL::getConnection();
   
  
  if ($result = $mysqli->query("SELECT valeur FROM PMG_PARAMETER where cle='BDD_STRUCT_VERSION'")) {
     $result->fetch_row();
     $version_struct_bd = intval($row[0]);
     $result->close();
  }
  else {
     // Erreur, create table param
     $version_struct_bd = -1;
  }


  if($version_struct_bd < 0) { 
     // Base vierge, on crée tout la structure
   $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_log` (".
                  "`DateHeure` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,".
                  "`AdresseIP` varchar(50) NOT NULL,".
                  "`log` varchar(255) NOT NULL,".
                  "PRIMARY KEY (`DateHeure`) ".
                  ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

    $mysqli->query("INSERT INTO `pmg_log` (`AdresseIP`,`log`) VALUES ('".$_SERVER["REMOTE_ADDR"]."','Installation structure database');");


     
    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_parametre` (".
                  "`CLE` varchar(50) NOT NULL,".
                  "`VALEUR` varchar(50) DEFAULT NULL, ".
                  "PRIMARY KEY (`CLE`),".
                  "UNIQUE KEY `CLE` (`CLE`)".
                  ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

    $mysqli->query("INSERT INTO `pmg_parametre` (`CLE`,`VALEUR`) VALUES ('BDD_STRUCT_VERSION','". SQL::getStructVersion() ."');");

    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_ligue` (".
                   "`ID_LIGUE` int(11) NOT NULL AUTO_INCREMENT,".
                   "`NOM` varchar(50),".
                   "PRIMARY KEY (`ID_LIGUE`)".
                   ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;");

    $mysqli->query("INSERT INTO `pmg_ligue` (`ID_LIGUE`, `NOM`) VALUES ".
                                "(1, 'Lyonnais'),".
                                "(2, 'Auvergne'),".
                                "(3, 'Franche-Comté'),".
                                "(4, 'Dauphiné-Savoie-Vivarais'),".
                                "(5, 'Pays de Loire'),".
                                "(6, 'Nord'),".
                                "(7, 'Aquitaine'),".
                                "(8, 'Ile de France'),".
                                "(9, 'Alsace'),".
                                "(10, 'Suisse');");

    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_club` (".
                   "`ID_CLUB` int(11) NOT NULL AUTO_INCREMENT,".
                   "`NOM` varchar(50) NOT NULL,".
                   "`VILLE` varchar(30) NOT NULL,".
                   "`SIGLE` varchar(10),".
                   "`REF_LIGUE` int(11),".
                   "`NOM_CONTACT` varchar(50),".
                   "`MAIL_CONTACT` varchar(50),".
                   "`ADRESSE_CONTACT` varchar(100),".
                   "`TEL_CONTACT` varchar(25),".
                   "PRIMARY KEY (`ID_CLUB`),".
                   "INDEX (REF_LIGUE),".
                      "FOREIGN KEY (REF_LIGUE) REFERENCES pmg_ligue(ID_LIGUE) ".
                        "ON UPDATE CASCADE ON DELETE RESTRICT ".
                 ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=44 ;");
    
   $mysqli->query("INSERT INTO `pmg_club` (`ID_CLUB`, `NOM`, `VILLE`, `REF_LIGUE`) VALUES ".
                   "(1, 'Alerte Sportive', 'ST ROMAIN EN GAL', 1),".
                   "(2, 'Cercle Bellecombe', 'LYON',  1),".
                   "(3, 'Le Cran', 'TASSIN',  1),".
                   "(4, 'Espérance et Vaillantes', 'BRIGNAIS', 1),".
                   "(5, 'Jeanne d\'Arc-Alouettes', 'CALUIRE', 1),".
                   "(6, 'Jeune Garde', 'VILLEFRANCHE S/SAONE', 1),".
                   "(7, 'Jeunesse Mornantaise', 'MORNANT', 1),".
                   "(8, 'L\'Etandard', 'LA TALAUDIERE', 1),".
                   "(9, 'Avant-Garde', 'ST ETIENNE', 1),".
                   "(10, 'Espérance', 'ROANNE', 1),".
                   "(11, 'AGS', 'VEAUCHE', 1),".
                   "(12, 'Patriote', 'CELLES/DUROLLE', 2),".
                   "(13, 'Jeanne d\'Arc', 'ST MARCELLIN', 4),".
                   "(14, 'Alerte-Gentianes', 'CHAMBERY', 4),".
                   "(15, 'Etoile', 'VOIRON', 4),".
                   "(16, 'Légion Viennoise', 'VIENNE', 4),".
                   "(17, 'Avant-Garde', 'LA TOUR DU PIN', 4),".
                   "(18, 'Fraternelle', 'BOURGOIN-JALLIEU', 4),".
                   "(19, 'Avenir Sportif', 'ROIFFIEUX', 4),".
                   "(20, 'Sentinelle des Alpes', 'GRENOBLE', 4),".
                   "(21, 'La Sportive', 'AUDINCOURT', 3),".
                   "(22, 'Les Gars de la Vallée', 'ORNANS', 3),".
                   "(23, 'Avenir du Dah', 'VALDAHON', 3),".
                   "(24, 'La Bousbotte', 'BESANCON', 3),".
                   "(25, 'Stéphanoise', 'MONTOIR DE BRETAGNE', 5),".
                   "(26, 'La Cambronnaise', 'ST SEBASTIEN / LOIRE', 5),".
                   "(27, 'Sport Loisir Amandinois', 'ST AMAND LES EAUX',6),".
                   "(28, 'Les Croisés St André', 'BAYONNE', 7),".
                   "(29, 'St George', 'ARGENTEUIL', 8),".
                   "(30, 'Stéla', 'BERGERAC', 7),".
                   "(31, 'Jeune Sévrienne', 'SEVRES', 8),".
                   "(32, 'CGS Locquidy', 'NANTES', 5),".
                   "(33, 'Jeanne d\'Arc', 'DAX', 7),".
                   "(34, 'Soc. St Léon', 'STE CROIX EN PLAINE', 9),".
                   "(35, 'ASC Bonne Garde', 'NANTES', 5),".
                   "(36, 'Ste Geneviève Sport', 'STE GENEVIEVE DES BOIS', 8),".
                   "(37, 'Amandinoise', 'ST AMAND LES EAUX', 6),".
                   "(38, 'Vigilante', 'DEUIL LA BARRE', 8),".
                   "(39, 'Avenir Gymnastique', 'CHEMILLE', 5),".
                   "(40, 'Cercle St Laurent', 'WASSELONNE', 9),".
                   "(41, 'Avenir', 'TRIEL', 8),".
                   "(42, 'Ass. Féminine et Sportive', 'CLUSES', 4),".
                   "(43, 'FSG', 'MONTREUX', 10);");
                   
    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_agres` (".
                   "`ID_AGRES` int(11) NOT NULL AUTO_INCREMENT,".
                   "`NOM` varchar(50),".
                   "`ICONE` varchar(50),".
                   "PRIMARY KEY (`ID_AGRES`)".
                   ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;");
                   
    $mysqli->query("INSERT INTO `pmg_agres` (`ID_AGRES`, `NOM`, `ICONE`) VALUES ".
                                "(1, 'Sol', 'Icone_Sol'),".
                                "(2, 'Arçons', 'Icone_Arçons'),".
                                "(3, 'Anneaux', 'Icone_Anneaux'),".
                                "(4, 'Saut', 'Icone_Saut'),".
                                "(5, 'Parallèles', 'Icone_Parallèles'),".
                                "(6, 'Fixe', 'Icone_Fixe'),".
                                "(7, 'Ateliers', 'Icone_Ateliers'),".
                                "(8, 'Production', 'Icone_Production'),".
                                "(9, 'Trampoline', 'Icone_Trampoline');");

    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_type_compet` (".
                   "`ID_TYPE_COMPET` int(11) NOT NULL AUTO_INCREMENT,".
                   "`COMMENTAIRE` varchar(255),".
                   "PRIMARY KEY (`ID_TYPE_COMPET`)".
                   ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;");

    $mysqli->query("INSERT INTO `pmg_type_compet` (`ID_TYPE_COMPET`, `COMMENTAIRE`) VALUES ".
                                "(1, 'Coupes Fédérales'),".
                                "(2, 'Championnat Individuel'),".
                                "(3, 'Coupe Nationale Interclub'),".
                                "(4, 'Championnat par Equipe');");

    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_competition` (".
                   "`ID_COMPETITITION` int(11) NOT NULL AUTO_INCREMENT,".
                   "`REF_TYPE_COMPETITION` int(11) NOT NULL,".
                   "`NOM` varchar(255),". 
                   "`LIEU`varchar(255),". 
                   "`DATE` DATE,". 
                   "`ORGANISATEUR` varchar(255),". 
                   "PRIMARY KEY (`ID_TYPE_COMPET`)".
                   "INDEX (REF_TYPE_COMPETITION),".
                      "FOREIGN KEY (REF_TYPE_COMPETITION) REFERENCES pmg_type_compet(ID_TYPE_COMPET) ".
                        "ON UPDATE CASCADE ON DELETE RESTRICT ".
                   ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");


    $mysqli->query("CREATE TABLE IF NOT EXISTS `pmg_user` (".
                   "`ID_USER` int(11) NOT NULL AUTO_INCREMENT,".
                   "`LOGIN` varchar(50),".
                   "`PASS` varchar(255),".
                   "`ADMIN` tinyint(1) NOT NULL DEFAULT '0',".
                   "`LISTE_DROIT` varchar(255),".
                   "`COMMENTAIRE` varchar(255),".
                   "PRIMARY KEY (`ID_USER`)".
                   ") ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;");

    $mysqli->query("INSERT INTO `pmg_user` (`ID_USER`, `LOGIN`, `PASS`, `ADMIN`, `LISTE_DROIT`) VALUES ".
                                "(1, 'Admin', '\$2y\$10\$8.rciBZRnGSiI2ZZgcUaxOiwL7s2yF69pRZB95tQqdl1kv8E8JzHS', 1, ''),".
                                "(2, 'Agres', '\$2y\$10\$vfJMW.1Ol8vAeredFRSNG.QGU2gHyvOSLSn35vT40Rh2lSpP99n26', 0, '1,2,3,4,5,6,7,8,9,10');");
                                      
      }
    
?>